package connectivity;

import java.io.InputStreamReader;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import models.Course;
import models.DigitalClass;
import models.DigitalClassQuestion;
import models.DigitalClassSession;
import models.Module;
import models.User;
import models.Vraag;
import models.CombiDeal;
import models.Announcement;

public class QueryManager {

    private final Dbmanager dbmanager;
    boolean logincheck;
    String queryresult;

    public QueryManager(Dbmanager dbmanager) {
        this.dbmanager = dbmanager;
    }

    public LinkedList<DigitalClass> getAllDigitalClasses() {
        DigitalClass digitalClass;
        LinkedList<DigitalClass> allDigitalClasses = new LinkedList<DigitalClass>();
        try {
            String sql = "SELECT * FROM digitalclass;";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                digitalClass = new DigitalClass();
                digitalClass.setDigitalClassID(Integer.parseInt(result.getString("digitalclassid")));
                digitalClass.setDate(result.getString("class_date"));
                digitalClass.setStartTime(result.getString("start_time"));
                digitalClass.setEndTime(result.getString("end_time"));
                digitalClass.setDescription(result.getString("description"));
                digitalClass.setModuleID(Integer.parseInt(result.getString("module_id")));
                digitalClass.setMaxStudents(Integer.parseInt(result.getString("maxstudents")));
                digitalClass.setTimeZone(result.getString("time_zone"));
                Module module = getModuleById(Integer.parseInt(result.getString("module_id")));
                digitalClass.setModule(module);
                Course course = getCoursbyid(digitalClass.getModule().getCourseID());
                digitalClass.setCourse(course);
                allDigitalClasses.add(digitalClass);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }

        return allDigitalClasses;
    }

    public DigitalClass geDigitalClassById(int digitalClassId) {
        DigitalClass digitalClass = null;
        try {
            String sql = "SELECT * FROM digitalclass WHERE digitalclassid = " + digitalClassId + ";";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                digitalClass = new DigitalClass();
                digitalClass.setDigitalClassID(Integer.parseInt(result.getString("digitalclassid")));
                digitalClass.setDate(result.getString("class_date"));
                digitalClass.setStartTime(result.getString("start_time"));
                digitalClass.setEndTime(result.getString("end_time"));
                digitalClass.setDescription(result.getString("description"));
                digitalClass.setModuleID(Integer.parseInt(result.getString("module_id")));
                digitalClass.setMaxStudents(Integer.parseInt(result.getString("maxstudents")));
                digitalClass.setTimeZone(result.getString("time_zone"));
                digitalClass.setModule(getModuleById(Integer.parseInt(result.getString("module_id"))));
                digitalClass.setCourse(getCoursbyid(digitalClass.getModule().getCourseID()));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }

        return digitalClass;
    }

    public LinkedList<DigitalClass> getAllSubscribedDigitalClasses(int userID) {
        DigitalClass digitalClass;
        LinkedList<DigitalClass> subscribedDigitalClasses = new LinkedList<DigitalClass>();
        try {
            String sql = "SELECT * FROM digitalclass_subscribe WHERE user_id = " + userID + ";";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                digitalClass = geDigitalClassById(Integer.parseInt(result.getString("digitalclass_id")));
                subscribedDigitalClasses.add(digitalClass);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }

        return subscribedDigitalClasses;
    }

    public DigitalClassSession getDigitalClassSession(int digitalClassId) {
        DigitalClassSession session;
        session = null;

        try {
            String sql = "SELECT * FROM digitalclass_session WHERE digitalclassid=" + digitalClassId;

            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                if (session == null) {
                    session = new DigitalClassSession(digitalClassId);
                }
                session.setClassActive(result.getBoolean("classActive"));
                session.setMelding(result.getString("melding"));
                session.setVideoActive(result.getBoolean("videoActive"));
                session.setVideoSource(result.getString("videoSource"));
                session.setVideoPlaying(result.getBoolean("videoPlaying"));
                session.setMediaActive(result.getBoolean("mediaActive"));
                session.setMediaSource(result.getString("mediaSource"));
                session.setMediaActivetype(result.getInt("mediaactivetype"));
                session.setPowerPointSlide(result.getInt("poweerpointslide"));
                session.setBlackBoardText(result.getString("blackboardtext"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return session;
    }

    public boolean dologincheck(String gebruikersnaam, String wachtwoord) {
        logincheck = false;
        try {
            String sql = "SELECT wachtwoord FROM users WHERE gebruikersnaam='" + gebruikersnaam + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                if (result.getString("wachtwoord").trim().equals(wachtwoord) == true) {
                    logincheck = true;
                }
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return logincheck;
    }

    public User getuser(String gebruikersnaam) {
        User user = new User();
        try {
            String sql = "SELECT gebruikersnaam, voornaam, achternaam, email, level, id FROM users WHERE gebruikersnaam='" + gebruikersnaam + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                user.setGebruikersnaam(result.getString("gebruikersnaam"));
                user.setVoornaam(result.getString("voornaam"));
                user.setAchternaam(result.getString("achternaam"));
                user.setEmail(result.getString("email"));
                user.setLevel(result.getInt("level"));
                user.setID(result.getInt("id"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return user;
    }

    public LinkedList<User> getallusers() {
        LinkedList<User> users = new LinkedList<User>();

        try {
            String sql = "SELECT * FROM users";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                User user = new User();
                user.setGebruikersnaam(result.getString("gebruikersnaam").trim());
                user.setVoornaam(result.getString("voornaam").trim());
                user.setAchternaam(result.getString("achternaam").trim());
                user.setEmail(result.getString("email").trim());
                user.setLevel(result.getInt("level"));
                user.setID(result.getInt("id"));
                user.setWachtwoord(result.getString("wachtwoord").trim());
                users.add(user);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return users;
    }

    public LinkedList<Integer> getallsubscribedcoursesbyid(int user_id) {
        LinkedList<Integer> courses = new LinkedList<Integer>();

        try {
            String sql = "SELECT DISTINCT course_id FROM subscribed WHERE user_id=" + user_id;
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                courses.add(Integer.parseInt(result.getString("course_id")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return courses;
    }

    public LinkedList<Course> getAllCourses() {
        LinkedList<Course> courses = new LinkedList<Course>();

        try {
            String sql = "SELECT * FROM course ORDER BY coursename ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Course course = new Course();
                course.setCourseDescription(result.getString("coursedescription"));
                course.setCourseID(result.getInt("courseid"));
                course.setCourseLanguage(result.getString("courselanguage"));
                course.setCourseName(result.getString("coursename"));
                course.setFinaltest(result.getBoolean("finaltest"));
                course.setPrice(result.getDouble("price"));
                course.setQuestions(isSelftestbycourse(result.getInt("courseid")));
                courses.add(course);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return courses;
    }

    public LinkedList<User> getAllCourseTeachers(int courseID) {
        LinkedList<User> teachers = new LinkedList<User>();
        try {
            String sql = "SELECT userid FROM courseteachers WHERE courseid=" + courseID + " ORDER BY userid ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                User teacher = getuserbyid(Integer.parseInt(result.getString("userid")));
                teachers.add(teacher);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return teachers;
    }

    public LinkedList<Course> getAllTeacherCourses(int teacherID) {
        LinkedList<Course> courses = new LinkedList<Course>();
        try {
            String sql = "SELECT courseid FROM courseteachers WHERE userid=" + teacherID + " ORDER BY userid ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Course course = getCoursbyid(Integer.parseInt(result.getString("courseid")));
                courses.add(course);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return courses;
    }

    public LinkedList<Module> getAllModules(int courseID) {
        LinkedList<Module> modules = new LinkedList<Module>();

        try {
            String sql = "SELECT * FROM coursemodule WHERE course_id=" + courseID;
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Module module = new Module();
                module.setModuleDescription(result.getString("module_beschrijving"));
                module.setCourseID(result.getInt("course_id"));
                module.setModuleID(result.getInt("module_id"));
                module.setModuleName(result.getString("module_naam"));
                module.setQuestions(isSelftestbymodule(result.getInt("module_id")));
                modules.add(module);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return modules;
    }

    public LinkedList<CombiDeal> getAllCombiDeals() {
        LinkedList<CombiDeal> combideals = new LinkedList<CombiDeal>();
        try {
            String sql = "SELECT * FROM combideals ORDER BY combidealid ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                int course1, course2, course3, course4, combidealid;
                double combiprice;
                try {
                    course1 = Integer.parseInt(result.getString("course1"));
                } catch (NumberFormatException ex) {
                    course1 = 0;
                }
                try {
                    course2 = Integer.parseInt(result.getString("course2"));
                } catch (NumberFormatException ex) {
                    course2 = 0;
                }
                try {
                    course3 = Integer.parseInt(result.getString("course3"));
                } catch (NumberFormatException ex) {
                    course3 = 0;
                }
                try {
                    course4 = Integer.parseInt(result.getString("course4"));
                } catch (NumberFormatException ex) {
                    course4 = 0;
                }

                combidealid = result.getInt("combidealid");
                combiprice = result.getDouble("combidealprice");
                CombiDeal combideal = new CombiDeal(course1, course2, course3, course4);
                combideal.setCombidealprice(combiprice);
                combideal.setCombidealid(combidealid);
                combideals.add(combideal);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return combideals;
    }

    public CombiDeal getCombiDealbyid(int combidealID) {
        CombiDeal combideal = null;
        try {
            String sql = "SELECT * FROM combideals WHERE combidealid=" + combidealID;
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                int course1, course2, course3, course4, combidealid;
                double combiprice;
                try {
                    course1 = Integer.parseInt(result.getString("course1"));
                } catch (NumberFormatException ex) {
                    course1 = 0;
                }
                try {
                    course2 = Integer.parseInt(result.getString("course2"));
                } catch (NumberFormatException ex) {
                    course2 = 0;
                }
                try {
                    course3 = Integer.parseInt(result.getString("course3"));
                } catch (NumberFormatException ex) {
                    course3 = 0;
                }
                try {
                    course4 = Integer.parseInt(result.getString("course4"));
                } catch (NumberFormatException ex) {
                    course4 = 0;
                }

                combidealid = result.getInt("combidealid");
                combiprice = result.getDouble("combidealprice");
                combideal = new CombiDeal(course1, course2, course3, course4);
                combideal.setCombidealprice(combiprice);
                combideal.setCombidealid(combidealid);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return combideal;
    }

    public LinkedList<Announcement> getAllAnnouncements(int courseID) {
        LinkedList<Announcement> announcements = new LinkedList<Announcement>();

        try {
            String sql = "SELECT * FROM announcements WHERE course_id=" + courseID;
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Announcement announcement = new Announcement();
                announcement.setAnnouncementInhoud(result.getString("announcement_entry"));
                announcement.setCourseID(result.getInt("course_id"));
                announcement.setAnnouncementID(result.getInt("announcement_id"));
                announcement.setAnnouncementName(result.getString("announcement_naam"));
                announcements.add(announcement);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return announcements;
    }

    public Module getModuleById(int module_id) {
        Module module = new Module();
        try {
            String sql = "SELECT * FROM coursemodule WHERE module_id=" + module_id;
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                module.setModuleDescription(result.getString("module_beschrijving").trim());
                module.setCourseID(result.getInt("course_id"));
                module.setModuleID(module_id);
                module.setModuleName(result.getString("module_naam").trim());
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return module;
    }

    public Course getCoursbyid(int course_id) {
        Course course = new Course();

        try {
            String sql = "SELECT * FROM course WHERE courseid=" + course_id;
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                course.setCourseDescription(result.getString("coursedescription"));
                course.setCourseID(course_id);
                course.setCourseLanguage(result.getString("courselanguage"));
                course.setCourseName(result.getString("coursename"));
                course.setFinaltest(result.getBoolean("finaltest"));
                course.setPrice(result.getDouble("price"));
                course.setQuestions(isSelftestbycourse(result.getInt(course_id)));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return course;
    }

    public Boolean isSelftestbymodule(int module_id) {
        Boolean vragen = false;
        try {
            String sql = "SELECT v.vraag_id, v.vraag_stelling, v.antwoord_a, v.antwoord_b, v.antwoord_c, v.antwoord_d, v.juiste_antwoord FROM vragen v, coursemodule m WHERE v.module_id = m.module_id AND v.module_id =" + module_id + "ORDER BY v.vraag_id ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                vragen = true;
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return vragen;
    }

    public Boolean isSelftestbycourse(int course_id) {
        Boolean vragen = false;
        try {
            String sql = "SELECT v.vraag_id, v.module_id, v.vraag_stelling, v.antwoord_a, v.antwoord_b, v.antwoord_c, v.antwoord_d, v.juiste_antwoord FROM vragen v, coursemodule m WHERE v.module_id = m.module_id AND m.course_id =" + course_id + "ORDER BY v.vraag_id ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                vragen = true;
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }

        return vragen;
    }

    public LinkedList<Vraag> getSelftestbymodule(int module_id) {
        LinkedList<Vraag> vragen = new LinkedList<Vraag>();

        try {
            String sql = "SELECT v.vraag_id, v.vraag_stelling, v.antwoord_a, v.antwoord_b, v.antwoord_c, v.antwoord_d, v.juiste_antwoord FROM vragen v, coursemodule m WHERE v.module_id = m.module_id AND v.module_id =" + module_id + "ORDER BY v.vraag_id ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Vraag vraag = new Vraag();

                vraag.setVraagid(Integer.parseInt(result.getString("vraag_id").trim()));
                vraag.setVraagstelling(result.getString("vraag_stelling").trim());
                vraag.setAntwoord_A(result.getString("antwoord_a").trim());
                vraag.setAntwoord_B(result.getString("antwoord_b").trim());
                vraag.setAntwoord_C(result.getString("antwoord_c").trim());
                vraag.setAntwoord_D(result.getString("antwoord_d").trim());
                vraag.setJuiste_antwoord(result.getString("juiste_antwoord").trim());

                vragen.add(vraag);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }

        return vragen;
    }

    public LinkedList<Vraag> getSelftestbycourse(int course_id) {
        LinkedList<Vraag> vragen = new LinkedList<Vraag>();

        try {
            String sql = "SELECT v.vraag_id, v.module_id, v.vraag_stelling, v.antwoord_a, v.antwoord_b, v.antwoord_c, v.antwoord_d, v.juiste_antwoord FROM vragen v, coursemodule m WHERE v.module_id = m.module_id AND m.course_id =" + course_id + "ORDER BY v.vraag_id ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Vraag vraag = new Vraag();

                vraag.setVraagid(Integer.parseInt(result.getString("vraag_id").trim()));
                vraag.setVraagstelling(result.getString("vraag_stelling").trim());
                vraag.setAntwoord_A(result.getString("antwoord_a").trim());
                vraag.setAntwoord_B(result.getString("antwoord_b").trim());
                vraag.setAntwoord_C(result.getString("antwoord_c").trim());
                vraag.setAntwoord_D(result.getString("antwoord_d").trim());
                vraag.setJuiste_antwoord(result.getString("juiste_antwoord").trim());

                vragen.add(vraag);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }

        return vragen;
    }

    public User getuserbyid(int ID) {
        User user = new User();
        try {
            String sql = "SELECT gebruikersnaam, voornaam, achternaam, email, level, id FROM users WHERE id='" + ID + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                user.setGebruikersnaam(result.getString("gebruikersnaam").trim());
                user.setVoornaam(result.getString("voornaam").trim());
                user.setAchternaam(result.getString("achternaam").trim());
                user.setEmail(result.getString("email").trim());
                user.setLevel(result.getInt("level"));
                user.setID(result.getInt("id"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return user;
    }

    public void deleteuserbyid(int ID) {
        String SQL_Delete = "DELETE FROM users WHERE id = '" + ID + "'";
        dbmanager.insertQuery(SQL_Delete);
    }

    public void deletemodulebyid(int ID) {
        String SQL_Delete = "DELETE FROM coursemodule WHERE module_id = '" + ID + "'";
        dbmanager.insertQuery(SQL_Delete);
    }

    public void deletecombidealbyid(int ID) {
        String SQL_Delete = "DELETE FROM combideals WHERE combidealid = '" + ID + "'";
        dbmanager.insertQuery(SQL_Delete);
    }

    public void deletecoursebyid(int ID) {
        String SQL_Delete = "DELETE FROM course WHERE courseid = '" + ID + "'";
        dbmanager.insertQuery(SQL_Delete);
    }

    public void updateSelftest(Vraag vraag) {
        String SQL_update = "UPDATE vragen SET vraag_stelling='" + vraag.getVraagstelling() + "', juiste_antwoord='" + vraag.getJuiste_antwoord() + "', antwoord_a='" + vraag.getAntwoord_A() + "', antwoord_b='" + vraag.getAntwoord_B() + "', antwoord_c='" + vraag.getAntwoord_C() + "', antwoord_d='" + vraag.getAntwoord_D() + "' WHERE vraag_id='" + vraag.getVraagid() + "'";
        dbmanager.insertQuery(SQL_update);
    }

    public void updateCombiDeal(CombiDeal combideal) {
        String SQL_update = "UPDATE combideals SET course1='" + combideal.getCourse1() + "', course2='" + combideal.getCourse2() + "', course3='" + combideal.getCourse3() + "', course4='" + combideal.getCourse4() + "', combidealprice='" + combideal.getCombidealprice() + "' WHERE combidealid='" + combideal.getCombidealid() + "'";
        dbmanager.insertQuery(SQL_update);
    }

    public void updateModule(Module module) {
        String SQL_update = "UPDATE coursemodule SET module_naam='" + module.getModuleName() + "', module_beschrijving='" + module.getModuleDescription() + "' WHERE module_id='" + module.getModuleID() + "'";
        dbmanager.insertQuery(SQL_update);
    }

    public void updateUser(User user) {
        String SQL_update = "UPDATE users SET gebruikersnaam='" + user.getGebruikersnaam() + "', voornaam='" + user.getVoornaam() + "', achternaam='" + user.getAchternaam() + "', email='" + user.getEmail() + "', level='" + user.getLevel() + "' WHERE id='" + user.getID() + "'";
        dbmanager.insertQuery(SQL_update);
    }

    public void updateCourse(Course course) {
        String SQL_update = "UPDATE course SET coursedescription='" + course.getCourseDescription() + "', courselanguage='" + course.getCourseLanguage() + "', coursename='" + course.getCourseName() + "', price='" + course.getPrice() + "' WHERE courseid='" + course.getCourseID() + "'";
        dbmanager.insertQuery(SQL_update);
    }

    public void updatePassword(User user) {
        String SQL_update = "UPDATE users SET wachtwoord='" + user.getWachtwoord() + "' WHERE id='" + user.getID() + "'";
        dbmanager.insertQuery(SQL_update);
    }

    public void updateDigitalClassSession(DigitalClassSession session) {
        String SQL_update = "UPDATE digitalclass_session SET melding = '" + session.getMelding() + "', classactive=" + session.isClassActive() + ", videoactive=" + session.isVideoActive() + ", videosource='" + session.getVideoSource() + "', videoplaying=" + session.isVideoPlaying() + ", mediaactive=" + session.isMediaActive() + ", mediaactivetype=" + session.getMediaActivetype() + ", mediasource='" + session.getMediaSource() + "', poweerpointslide=" + session.getPowerPointSlide() + ", blackboardtext='" + session.getBlackBoardText() + "' WHERE digitalclassid=" + session.getDigitalClassId();
        dbmanager.insertQuery(SQL_update);
    }

    public void answerQuestion(int questionID, String answer) {
        String SQL_update = "UPDATE digitalclass_question SET answer='" + answer + "' WHERE questionid='" + questionID + "'";
        dbmanager.insertQuery(SQL_update);
    }

    public void addsubscribed(int userid, int courseid, Date sqldatum) {
        String SQL_adduser = "INSERT INTO subscribed (user_id, course_id, datesubscribed) VALUES(" + userid + ", " + courseid + ", '" + sqldatum + "')";
        dbmanager.insertQuery(SQL_adduser);
    }

    public void addSubscribedDigitalClass(int userid, int digitalClassID) {
        String SQL_adduser = "INSERT INTO digitalclass_subscribe (user_id, digitalclass_id) VALUES(" + userid + ", " + digitalClassID + ")";
        dbmanager.insertQuery(SQL_adduser);
    }

    public void addselftestquestion(Vraag vraag, int moduleid) {
        String SQL_adduser = "INSERT INTO vragen (module_id, vraag_stelling, antwoord_a, antwoord_b, antwoord_c, antwoord_d, juiste_antwoord) VALUES(" + moduleid + ", '" + vraag.getVraagstelling() + "', '" + vraag.getAntwoord_A() + "', '" + vraag.getAntwoord_B() + "', '" + vraag.getAntwoord_C() + "', '" + vraag.getAntwoord_D() + "', '" + vraag.getJuiste_antwoord() + "')";
        dbmanager.insertQuery(SQL_adduser);
    }

    public void addcombideal(CombiDeal combideal) {
        String SQL_adduser = "INSERT INTO combideals (course1, course2, course3, course4, combidealprice) VALUES('" + combideal.getCourse1() + "', '"
                + combideal.getCourse2() + "', '" + combideal.getCourse3() + "', '" + combideal.getCourse4() + "', '" + combideal.getCombidealprice() + "')";
        dbmanager.insertQuery(SQL_adduser);
    }

    public void adduser(User newUser) {
        String SQL_adduser = "INSERT INTO users (gebruikersnaam, wachtwoord, voornaam, achternaam, email, level) VALUES('" + newUser.getGebruikersnaam() + "', '"
                + newUser.getWachtwoord() + "', '" + newUser.getVoornaam() + "', '" + newUser.getAchternaam() + "', '" + newUser.getEmail() + "', '" + newUser.getLevel() + "')";
        dbmanager.insertQuery(SQL_adduser);
    }

    public void addDigitalClassSession(DigitalClassSession digitalClass) {
        String SQL_addClass = "INSERT INTO digitalclass_session(digitalclassid, melding, classactive, videoactive, videosource, "
                + "videoplaying, mediaactive, mediaactivetype, mediasource, poweerpointslide, blackboardtext)"
                + "VALUES (" + digitalClass.getDigitalClassId() + ", '" + digitalClass.getMelding() + "', " + digitalClass.isClassActive() + ", " + digitalClass.isVideoActive() + ", '" + digitalClass.getVideoSource() + "',"
                + "" + digitalClass.isVideoPlaying() + ", " + digitalClass.isMediaActive() + ", " + digitalClass.getMediaActivetype() + ", '" + digitalClass.getMediaSource() + "', " + digitalClass.getPowerPointSlide() + ", '" + digitalClass.getBlackBoardText() + "');";
        dbmanager.insertQuery(SQL_addClass);
    }

    public int addCourse(Course course) {
        String SQL_addCourse = "INSERT INTO course (coursename, courselanguage, coursedescription, finaltest, price) VALUES('" + course.getCourseName() + "', '"
                + course.getCourseLanguage() + "', '" + course.getCourseDescription() + "', '" + course.isFinaltest() + "', '" + course.getPrice() + "')";
        dbmanager.insertQuery(SQL_addCourse);
        ResultSet result = dbmanager.doQuery("SELECT max(courseid) AS lastid FROM course");
        int lastID = 0;
        try {
            result.next();
            lastID = Integer.parseInt(result.getString("lastid").trim());
        } catch (Exception E) {
            lastID = -1;
        }
        return lastID;
    }

    public void planDigitalClass(DigitalClass digitalClass) {
        String SQL_addClass = "INSERT INTO digitalclass (class_date, start_time, end_time, time_zone, description, module_id, maxstudents) VALUES('" + digitalClass.getDate() + "', '"
                + digitalClass.getStartTime() + "', '" + digitalClass.getEndTime() + "', '" + digitalClass.getTimeZone() + "', '" + digitalClass.getDescription() + "', '" + digitalClass.getModuleID() + "', '" + digitalClass.getMaxStudents() + "')";
        dbmanager.insertQuery(SQL_addClass);
    }

    public void addModule(Module module) {
        String SQL_addModule = "INSERT INTO coursemodule (module_naam, module_beschrijving, course_id) VALUES('" + module.getModuleName() + "', '"
                + module.getModuleDescription() + "', '" + module.getCourseID() + "')";
        dbmanager.insertQuery(SQL_addModule);
    }

    public void addteachertocourse(int teacherid, int courseid) {
        String SQL_addQuestion = "INSERT INTO courseteachers (userid, courseid) VALUES('" + teacherid + "', '" + courseid + "')";
        dbmanager.insertQuery(SQL_addQuestion);
    }

    public void addQuestion(String question, int digitalClassID) {
        String SQL_addQuestion = "INSERT INTO DigitalClass_Question (digitalclassID, content) VALUES('" + digitalClassID + "', '" + question + "')";
        dbmanager.insertQuery(SQL_addQuestion);
    }

    public void addAnnouncement(Announcement announcement) {
        String SQL_addAnnouncement = "INSERT INTO announcements (announcement_naam, announcement_entry, course_id) VALUES('" + announcement.getAnnouncementName() + "', '"
                + announcement.getAnnouncementInhoud() + "', '" + announcement.getCourseID() + "')";
        dbmanager.insertQuery(SQL_addAnnouncement);
    }

    public ArrayList getQuestions(int digitalClassID) {
        ArrayList questions = new ArrayList();
        try {
            String sql = "SELECT * FROM digitalclass_question WHERE digitalclassid = " + digitalClassID + " AND answer IS NULL";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                DigitalClassQuestion question = new DigitalClassQuestion();
                question.setAnswer(result.getString("answer"));
                question.setContent(result.getString("content"));
                question.setDigitalClassID(result.getInt("digitalclassid"));
                question.setQuestionID(result.getInt("questionid"));
                questions.add(question);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return questions;
    }

    public ArrayList getQuestionsAnswered(int digitalClassID) {
        ArrayList questions = new ArrayList();
        try {
            String sql = "SELECT * FROM digitalclass_question WHERE digitalclassid = " + digitalClassID + " AND answer IS NOT NULL ORDER BY questionid desc";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                DigitalClassQuestion question = new DigitalClassQuestion();
                question.setAnswer(result.getString("answer"));
                question.setContent(result.getString("content"));
                question.setDigitalClassID(result.getInt("digitalclassid"));
                question.setQuestionID(result.getInt("questionid"));
                questions.add(question);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return questions;
    }
}
